﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Vertica.Data.VerticaClient;
using System.Data;
using System.IO;

namespace Hp.Vertica.Client.Internal
{
    public class VerticaDatabase
    {
        public VerticaDatabase() { }
        public VerticaDatabase(String host, String user, String password, String dbName)
        {
            this.Host = host;
            this.User = user;
            this.Password = password;
            this.Database = dbName;
        }

        public String Host { get; set; }
        public String User { get; set; }
        public String Password { get; set; }
        public String Database { get; set; }

        public String Name
        {
            get
            {
                return this.Database + " (" + this.Host + " - " + this.User + ")";
            }
        }

        public VerticaDatabase Clone()
        {
            return new VerticaDatabase(this.Host, this.User, this.Password, this.Database);
        }

        public String ConnectionString
        {
            get
            {
                VerticaConnectionStringBuilder sb = new VerticaConnectionStringBuilder();
                sb.Host = this.Host;
                sb.User = this.User;
                sb.Password = this.Password;
                sb.Database = this.Database;
                return sb.ToString();
            }
        }

        public Category SystemTables
        {
            get
            {
                if (this._categories == null)
                {
                    this.RefereshAll();
                }
                return this._categories["SYSTEM TABLE"];
            }
        }

        public Category UserTables
        {
            get
            {
                if (this._categories == null)
                {
                    this.RefereshAll();
                }
                return this._categories["TABLE"];
            }
        }

        public Category UserViews
        {
            get
            {
                if (this._categories == null)
                {
                    this.RefereshAll();
                }
                return this._categories["VIEW"];
            }
        }

        public List<Category> Categories
        {
            get
            {
                if (_categories == null)
                {
                    this.RefereshAll();
                }
                return _categories.Values.OrderBy(c => c.Name).ToList();
            }
        }

        public List<Schema> Schemas
        {
            get
            {
                if (_schemas == null)
                {
                    this.RefereshAll();
                }
                return _schemas.Values.OrderBy(c => c.Name).ToList();
            }
        }

        public List<Table> Tables
        {
            get
            {
                if (this._objects == null)
                {
                    this.RefereshAll();
                }
                return _objects.OrderBy(c => c.Category.Name).ThenBy(c => c.Schema.Name).ThenBy(c => c.Name).ToList();
            }
        }

        internal List<Schema> GetSchemasByCategory(Category category)
        {
            if (this._objects == null)
            {
                this.RefereshAll();
            }
            Dictionary<String, Schema> schemas = new Dictionary<string, Schema>();
            foreach (var a in this._objects.Where(c => c.Category == category))
            {
                if (!schemas.ContainsKey(a.Schema.Name))
                {
                    schemas.Add(a.Schema.Name, a.Schema);
                }
            }
            return schemas.Values.OrderBy(c => c.Name).ToList();
        }

        internal List<Category> GetCategoriesBySchema(Schema schema)
        {
            if (this._objects == null)
            {
                this.RefereshAll();
            }

            Dictionary<String, Category> cats = new Dictionary<string, Category>();
            foreach (var a in this._objects.Where(c => c.Schema == schema))
            {
                if (!cats.ContainsKey(a.Category.Name))
                {
                    cats.Add(a.Category.Name, a.Category);
                }
            }
            return cats.Values.OrderBy(c => c.Name).ToList();
        }

        internal List<Table> GetTables(Schema schema)
        {
            if (this._objects == null)
            {
                this.RefereshAll();
            }
            return this._objects.Where(c => c.Schema == schema).ToList();
        }

        internal List<Table> GetTables(Category category)
        {
            if (this._objects == null)
            {
                this.RefereshAll();
            }
            return this._objects.Where(c => c.Category == category).ToList();
        }

        internal List<Table> GetTables(Category category, Schema schema)
        {
            if (this._objects == null)
            {
                this.RefereshAll();
            }
            return this._objects.Where(c => c.Category == category && c.Schema == schema).ToList();
        }

        public void Connect()
        {
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                conn.Close();
            }
        }

        public void RefereshAll()
        {
            this.refreshCategories();
            this.refreshSchemas();
            this.refreshTables();
            this.refreshColumns();
        }

        private Dictionary<String, Schema> _schemas;
        private void refreshSchemas()
        {
            _schemas = new Dictionary<String, Schema>();
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                foreach (DataRow dr in conn.GetSchema("SchemasOnly").Rows)
                {
                    String sName = dr[0].ToString();
                    if (!_schemas.ContainsKey(sName))
                    {
                        Schema sch = new Schema(sName, this);
                        _schemas.Add(sName, sch);
                    }
                }
                conn.Close();
            }
        }

        private Dictionary<String, Category> _categories;
        private void refreshCategories()
        {
            _categories = new Dictionary<String, Category>();
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                foreach (DataRow dr in conn.GetSchema("TableTypesOnly").Rows)
                {
                    String sName = dr[0].ToString();
                    if (!_categories.ContainsKey(sName))
                    {
                        Category sch = new Category(sName, this);
                        _categories.Add(sName, sch);
                    }
                }
                conn.Close();
            }
        }

        private List<Table> _objects;
        private void refreshTables()
        {
            //if (this._schemas == null) { this.refreshSchemas(); }
            //if (this._categories == null) { this.refreshCategories(); }

            _objects = new List<Table>();
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                foreach (DataRow dr in conn.GetSchema("Tables").Rows)
                {
                    //String dbName = dr["TABLE_CAT"].ToString();
                    String sName = dr["TABLE_SCHEM"].ToString();
                    String tName = dr["TABLE_NAME"].ToString();
                    String tType = dr["TABLE_TYPE"].ToString();
                    String remark = dr["REMARKS"].ToString();

                    Table obj = new Table(tName);
                    obj.Remark = remark;
                    obj.Category = this._categories[tType];
                    obj.Schema = this._schemas[sName];
                    _objects.Add(obj);
                }
                conn.Close();

            }
        }

        public void ExecuteScript(String script)
        {
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = script;
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        public void AddSchema(Schema s)
        {
            this.ExecuteScript(s.CreateScript);
        }

        public void AddTable(Table t)
        {
            this.ExecuteScript(t.CreateScript);
        }

        public long BulkLoad(DataTable data, Table target)
        {
            StringBuilder sb = new StringBuilder();
            foreach (DataRow row in data.Rows)
            {
                int colIndex = 0;
                foreach (DataColumn column in data.Columns)
                {
                    if (colIndex > 0)
                    {
                        sb.Append(Table.ColumnDelimiter);
                    }
                    sb.Append(row.IsNull(column) ? String.Empty : row[column].ToString());
                    colIndex++;
                }
                sb.Append(Table.RowTerminator);
            }
            byte[] buff = Encoding.Default.GetBytes(sb.ToString());
            using (MemoryStream stream = new MemoryStream(buff, 0, buff.Length))
            {
                stream.Flush();
                stream.Position = 0;

                using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
                {
                    conn.Open();
                    VerticaCopyStream cp = new VerticaCopyStream(conn, target.BulkCopyScript);
                    cp.Start();
                    cp.AddStream(stream, false);
                    cp.Execute();
                    conn.Close();
                    return cp.RowCount;
                }
            }
        }

        private Dictionary<String, List<Column>> _columns;
        private void refreshColumns()
        {
            _columns = new Dictionary<string, List<Column>>();
            using (VerticaConnection conn = new VerticaConnection(this.ConnectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT c.column_name
                                    , t.type_name
                                    , ISNULL(c.character_maximum_length, 0) AS max_length
                                    , ISNULL(c.numeric_precision, isnull(c.datetime_precision, 0)) AS precision
                                    , ISNULL(c.numeric_scale, 0) AS scale
                                    , c.is_nullable
                                    , c.is_identity
                                    , c.table_schema
                                    , c.table_name
                                    FROM v_catalog.columns c
                                    JOIN v_catalog.types t
                                    ON c.data_type_id = t.type_id
                                    UNION ALL
                                    SELECT c.column_name
                                    , t.type_name
                                    , ISNULL(c.character_maximum_length, 0) AS max_length
                                    , ISNULL(c.numeric_precision, isnull(c.datetime_precision, 0)) AS precision
                                    , ISNULL(c.numeric_scale, 0) AS scale
                                    , ISNULL(c.is_nullable,false) AS is_nullable
                                    , false AS is_identity
                                    , c.table_schema
                                    , c.table_name
                                    FROM v_catalog.system_columns c
                                    JOIN v_catalog.types t
                                    ON c.data_type_id = t.type_id
                                    UNION ALL
                                    SELECT c.column_name
                                    , t.type_name
                                    , ISNULL(c.character_maximum_length, 0) AS max_length
                                    , ISNULL(c.numeric_precision, isnull(c.datetime_precision, 0)) AS precision
                                    , ISNULL(c.numeric_scale, 0) AS scale
                                    , false AS is_nullable
                                    , false AS is_identity
                                    , c.table_schema
                                    , c.table_name
                                    FROM v_catalog.view_columns c
                                    JOIN v_catalog.types t
                                    ON c.data_type_id = t.type_id";
                using (VerticaDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        String schemaName = dr["table_schema"].ToString();
                        String tableName = dr["table_name"].ToString();
                        String key = schemaName.ToUpper() + "." + tableName.ToUpper();
                        if (!_columns.ContainsKey(key))
                        {
                            _columns.Add(key, new List<Column>());
                        }
                        Column col = new Column();
                        col.Name = dr["column_name"].ToString();
                        col.IsIdentity = Boolean.Parse(dr["is_identity"].ToString());
                        var v = dr["is_nullable"].ToString();
                        col.IsNullable = Boolean.Parse(dr["is_nullable"].ToString());
                        var size = Int32.Parse(dr["max_length"].ToString());
                        var precision = Int32.Parse(dr["precision"].ToString());
                        var scale = Int32.Parse(dr["scale"].ToString());
                        col.DataType = VerticaDataType.CreateVerticaDataType(dr["type_name"].ToString(), size, precision, scale);
                        _columns[key].Add(col);
                    }
                }
            }
        }

        public List<Column> GetColumns(Table t)
        {
            if (_columns == null)
            {
                RefereshAll();
            }
            String key = t.Schema.Name.ToUpper() + "." + t.Name.ToUpper();
            if (this._columns.ContainsKey(key))
            {
                return this._columns[key];
            }
            else
            {
                return null;
            }
        }
    }
}
